package com.cheney.demo.easypoi;

import cn.afterturn.easypoi.csv.CsvImportUtil;
import cn.afterturn.easypoi.csv.entity.CsvImportParams;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.cheney.demo.easypoi.entity.QooDTO;
import com.cheney.demo.easypoi.test.excel.read.FileUtilTest;
import com.cheney.demo.easypoi.test.excel.read.MapImportHandler;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author chensi
 * @date 2022/8/29 15:06
 */
@Slf4j
public class 导出购买模板生成文件 {

    /**
     * 从亚马逊导入
     */
    @Test
    public void testReadAmazon() {
        ImportParams params = new ImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);
//        params.setDataHandler(new MapImportHandler());
        List<Map<String, Object>> targetList = new ArrayList<>();
        List<Map<String, Object>> list = ExcelImportUtil.importExcel(
                new File(FileUtilTest.getWebRootPath("read/amazon.xlsx")), Map.class, params);
        log.info("list={}", JSON.toJSON(list));
        for (Map<String, Object> item : list) {
            Map<String, Object> map = new HashMap<>();
            map.put("orderId", item.get("order-id"));
            map.put("lineItemNumber", 1);
            map.put("B2BItemCode", item.get("sku"));
            map.put("shipToName", item.get("recipient-name"));
            map.put("shipToEmail", item.get("buyer-email"));
            String phone = item.get("buyer-phone-number") == null ? "" : item.get("buyer-phone-number").toString();
            if (StrUtil.isNotBlank(phone)) {
                String three = StrUtil.sub(phone, phone.length() - 1 - 3, phone.length());
                String two = StrUtil.sub(phone, phone.length() - 1 - 7, phone.length() - 1 - 3);
                String one = "0" + StrUtil.sub(phone, 0, phone.length() - 1 - 7);
                map.put("shipToPhone", one + "-" + two + "-" + three);
            }
            map.put("shipToPostalCode", item.get("ship-postal-code"));
            String address1 = item.get("ship-address-1") == null ? "" : item.get("ship-address-1").toString();
            String address2 = item.get("ship-address-2") == null ? "" : item.get("ship-address-2").toString();
            String address3 = item.get("ship-address-3") == null ? "" : item.get("ship-address-3").toString();
            map.put("shipToQty", item.get("quantity-to-ship"));
            map.put("shipToAddressDetail", StrUtil.concat(true, address1, address2, address3));
            map.put("shipToCity", item.get("ship-city"));
            map.put("shipToState", item.get("ship-state"));
            map.put("shipToCountry", item.get("ship-country"));
            String start = item.get("scheduled-delivery-start-date") == null ? "" : item.get("scheduled-delivery-start-date").toString();
            if (StrUtil.isNotBlank(start)) {
                start = StrUtil.sub(start, 0, 16);
            }
            String end = item.get("scheduled-delivery-end-date") == null ? "" : item.get("scheduled-delivery-end-date").toString();
            if (StrUtil.isNotBlank(end)) {
                end = StrUtil.sub(end, 11, 16);
            }
            String requestedDeliveryDate = start + "-" + end;
            if (StrUtil.equals(requestedDeliveryDate, "-")) {
                requestedDeliveryDate = "";
            }
            map.put("requestedDeliveryDate", requestedDeliveryDate);
            targetList.add(map);
        }

        this.generate(targetList);
        //拆分
        //order-id ==> OrderId
        //order-item-id ==> LineItemNumber
        //sku ==> B2BItemCode
        //recipient-name ==> ShipToName
        //buyer-email ==> ShipToEmail
        //buyer-phone-number ==> ShipToPhone
        //ship-postal-code ==> ShipToPostalCode
        //ship-address-1 + ship-address-2 + ship-address-3 ==> ShipToAddressDetail
        //ship-city ==> ShipToCity
        //ship-state ==> ShipToState
        //ship-country ==> ShipToCountry
        //scheduled-delivery-start-date -  scheduled-delivery-end-date ==> RequestedDeliveryDate
        //quantity-to-ship ==> ShipToQty
        //07-6572-1990也行
        //2022-09-17T08:00:00+09:00,2022-09-17T12:00:00+09:00，这个就是2022-09-17T08:00-12:00
        //从左到右匹配：市区町村郡放到city  都道府県放到state  如果匹配到多个则只放第一个
    }

    /**
     * 从qoo导入
     */
    @SneakyThrows
    @Test
    public void testReadQoo() {
        CsvImportParams params = new CsvImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);
        params.setDataHandler(new MapImportHandler());
        //解决CSV导入的时候出现双重"的问题
        params.setSpiltMark("\"");
//        List<Map<String, Object>> list = CsvImportUtil.importCsv(
//                new FileInputStream(FileUtilTest.getWebRootPath("read/qoo.csv")), Map.class, params);
        List<Map<String, Object>> list = CsvImportUtil.importCsv(
                new FileInputStream(FileUtilTest.getWebRootPath("read/qoo.csv")), QooDTO.class, params);
        for (int i = 0; i < list.size(); i++) {
            System.out.println(JSON.toJSON(list.get(i)));
        }
        //拆分
    }

    /**
     * 从yahoo导入
     */
    @SneakyThrows
    @Test
    public void testReadYahoo() {
        CsvImportParams params = new CsvImportParams();
        params.setEncoding("utf-8");
        params.setTitleRows(0);
        params.setHeadRows(1);
        params.setDataHandler(new MapImportHandler());
        List<Map<String, Object>> list = CsvImportUtil.importCsv(
                new FileInputStream(FileUtilTest.getWebRootPath("read/yahooorderbase.csv")), Map.class, params);
        for (int i = 0; i < list.size(); i++) {
            System.out.println(JSON.toJSON(list.get(i)));
        }
        //拆分
    }

    @SneakyThrows
    private void generate(List<Map<String, Object>> list) {
        TemplateExportParams params = new TemplateExportParams(
                "excel/购买模板.xls", 0);
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("list", list);
        Workbook book = ExcelExportUtil.exportExcel(params, resultMap);
        File savefile = new File("D:/data/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream("D:/data/excel/购买模板" + RandomUtil.randomString(6) + ".xls");
        book.write(fos);
        fos.close();
    }

    /**
     * 输出excel
     */
    @Test
    public void testGenerate() throws Exception {
        TemplateExportParams params = new TemplateExportParams(
                "excel/购买模板.xls", 0);
        List<Map<String, String>> list = new ArrayList<>();
        Map<String, String> map = new HashMap<>();
        map.put("salesPlatform", "001");
        map.put("orderId", "001");
        map.put("lineItemNumber", "001");
        map.put("B2BItemCode", "001");
        map.put("shipToQty", "001");
        map.put("deliveryToFBAWarehouse", "001");
        map.put("shipToName", "001");
        map.put("shipToEmail", "001");
        map.put("shipToPhone", "001");
        map.put("shipToPostalCode", "001");
        map.put("shipToAddressDetail", "001");
        map.put("shipToCity", "001");
        map.put("shipToState", "001");
        map.put("shipToCountry", "001");
        map.put("shipToServiceLevel", "001");
        map.put("shipToAttachmentUrl", "001");
        map.put("orderDate", "001");
        map.put("requestedDeliveryDate", "001");
        map.put("buyerBrand", "xx");
        map.put("buyerPlatformSku", "001");
        map.put("buyerSkuDescription", "001");
        map.put("buyerSkuCommercialValue", "001");
        map.put("buyerSkuLink", "001");
        map.put("orderComments", "001");


        for (int i = 0; i < 18; i++) {
            list.add(map);
        }

        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("list", list);
        Workbook book = ExcelExportUtil.exportExcel(params, resultMap);
        File savefile = new File("D:/data/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream("D:/data/excel/购买模板" + RandomUtil.randomString(6) + ".xls");
        book.write(fos);
        fos.close();

    }
}
